package com.ruoyi.generator.util;

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;
import com.alibaba.druid.sql.visitor.SQLASTOutputVisitor;
import com.alibaba.druid.util.JdbcUtils;
import com.ruoyi.common.constant.GenConstants;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.generator.domain.GenTable;
import com.ruoyi.generator.domain.GenTableColumn;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.replace.Replace;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.util.TablesNamesFinder;
import org.apache.velocity.VelocityContext;

import java.io.StringReader;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


public class DataPermissionSqlUtil {


    private static CCJSqlParserManager pm = new CCJSqlParserManager();

    /** 项目空间路径 */
    private static final String PROJECT_PATH = "main/java";

    /** mybatis空间路径 */
    private static final String MYBATIS_PATH = "main/resources/mapper";

    /** 默认上级菜单，系统工具 */
    private static final String DEFAULT_PARENT_MENU_ID = "3";

    private static Pattern linePattern = Pattern.compile("_(\\w)");

    //首字母转小写
    public static String toLowerCaseFirstOne(String s){
        if(Character.isLowerCase(s.charAt(0)))
            return s;
        else
            return (new StringBuilder()).append(Character.toLowerCase(s.charAt(0))).append(s.substring(1)).toString();
    }


    //首字母转大写
    public static String toUpperCaseFirstOne(String s){
        if(Character.isUpperCase(s.charAt(0)))
            return s;
        else
            return (new StringBuilder()).append(Character.toUpperCase(s.charAt(0))).append(s.substring(1)).toString();
    }

    /**
     * 获取文件名
     */
    public static String getFileName(String template,String businessName, String packageName,String className)
    {
        // 文件名称
        String fileName = "";
        String mybatisPath = MYBATIS_PATH + "/" + businessName;
        String javaPath = PROJECT_PATH + "/" + StringUtils.replace(packageName, ".", "/");
        if (template.contains("mapper.xml.vm"))
        {
            fileName = StringUtils.format("{}/{}Mapper.xml", mybatisPath, className);
        }
        else if (template.contains("domain.java.vm"))
        {
            fileName = StringUtils.format("{}/domain/vo/{}Vo.java", javaPath, className);
        }
        else if (template.contains("mapper.java.vm"))
        {
            fileName = StringUtils.format("{}/mapper/{}Mapper.java", javaPath, className);
        }
        else if(template.contains("service.java.vm")){
            fileName = StringUtils.format("{}/service/{}Service.java", javaPath, className);
        }
        else if (template.contains("controller.java.vm"))
        {
            fileName = StringUtils.format("{}/controller/{}Controller.java", javaPath, className);
        }
        return fileName;
    }


    /**
     * 设置模板变量信息
     *
     * @return 模板列表
     */
    public static VelocityContext prepareContext(Map<String,Object> map)
    {

        VelocityContext velocityContext = new VelocityContext();
        velocityContext.put("packageName",map.get("packageName"));
        velocityContext.put("importList", getImportList());
        velocityContext.put("functionName",map.get("functionName"));
        velocityContext.put("businessName",map.get("businessName"));
        velocityContext.put("className",map.get("className"));
        velocityContext.put("newClassName",map.get("newClassName"));
        velocityContext.put("columns", map.get("columns"));
        velocityContext.put("sql", map.get("sql"));
        return velocityContext;
    }

    /**
     * 根据列类型获取导入包
     *
     * @return 返回需要导入的包列表
     */
    public static HashSet<String> getImportList()
    {
        HashSet<String> importList = new HashSet<String>();
        importList.add("java.util.List");
        importList.add("java.util.Date");
        importList.add("com.fasterxml.jackson.annotation.JsonFormat");
        importList.add("java.math.BigDecimal");
        return importList;
    }
    /**
     * 获取模板信息
     *
     * @return 模板列表
     */
    public static List<String> getTemplateList()
    {
        List<String> templates = new ArrayList<String>();
        templates.add("sqlvm/java/domain.java.vm");
        templates.add("sqlvm/xml/mapper.xml.vm");
        templates.add("sqlvm/java/mapper.java.vm");
        templates.add("sqlvm/java/service.java.vm");
        templates.add("sqlvm/java/controller.java.vm");
        return templates;
    }

    /** 下划线转驼峰 */
    public static String lineToHump(String str) {
        str = str.toLowerCase();
        Matcher matcher = linePattern.matcher(str);
        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            matcher.appendReplacement(sb, matcher.group(1).toUpperCase());
        }
        matcher.appendTail(sb);
        return sb.toString();
    }

    // 去掉所有标点符号
    public static String deleteBiaoDian(String s){
        String str=s.replaceAll("[`~!@#$%^&*()+=|{}':;',\\[\\].<>/?~！@#￥%……& amp;*（）——+|{}【】‘；：”“’。，、？|-]", "");
        return str;
    }

    // 获取函数的返回类型
    public static String getHanshuType(String column){
        column = column.toLowerCase();
        if(column.indexOf("ascii(") != -1
                || column.indexOf("char_length(") != -1
                || column.indexOf("character_length(") != -1
                || column.indexOf("concat(") != -1
                || column.indexOf("concat_ws(") != -1
                || column.indexOf("field(") != -1
                || column.indexOf("find_in_set(") != -1
                || column.indexOf("format(") != -1
                || column.indexOf("insert(") != -1
                || column.indexOf("locate(") != -1
                || column.indexOf("lcase(") != -1
                || column.indexOf("left(") != -1
                || column.indexOf("lower(") != -1
                || column.indexOf("lpad(") != -1
                || column.indexOf("ltrim(") != -1
                || column.indexOf("mid(") != -1
                || column.indexOf("position(") != -1
                || column.indexOf("repeat(") != -1
                || column.indexOf("replace(") != -1
                || column.indexOf("reverse(") != -1
                || column.indexOf("right(") != -1
                || column.indexOf("rpad(") != -1
                || column.indexOf("rtrim(") != -1
                || column.indexOf("space(") != -1
                || column.indexOf("strcmp(") != -1
                || column.indexOf("substr(") != -1
                || column.indexOf("substring(") != -1
                || column.indexOf("substring_index(") != -1
                || column.indexOf("trim(") != -1
                || column.indexOf("ucase(") != -1
                || column.indexOf("upper(") != -1
                || column.indexOf("bin(") != -1
                || column.indexOf("binary(") != -1
                || column.indexOf("case") != -1
                || column.indexOf("cast(") != -1
                || column.indexOf("coalesce(") != -1
                || column.indexOf("connection_id(") != -1
                || column.indexOf("conv(") != -1
                || column.indexOf("convert(") != -1
                || column.indexOf("current_user(") != -1
                || column.indexOf("database(") != -1
                || column.indexOf("if(") != -1
                || column.indexOf("ifnull(") != -1
                || column.indexOf("isnull(") != -1
                || column.indexOf("last_insert_id(") != -1
                || column.indexOf("nullif(") != -1
                || column.indexOf("session_user(") != -1
                || column.indexOf("system_user(") != -1
                || column.indexOf("user(") != -1
                || column.indexOf("version(") != -1
        ){
            return "String";
        } else if(column.indexOf("abs(") != -1
                || column.indexOf("acos(") != -1
                || column.indexOf("asin(") != -1
                || column.indexOf("atan(") != -1
                || column.indexOf("atan2(") != -1
                || column.indexOf("avg(") != -1
                || column.indexOf("ceil(") != -1
                || column.indexOf("ceiling(") != -1
                || column.indexOf("cos(") != -1
                || column.indexOf("cot(") != -1
                || column.indexOf("count(") != -1
                || column.indexOf("degrees(") != -1
                || column.indexOf("div") != -1
                || column.indexOf("exp(") != -1
                || column.indexOf("floor(") != -1
                || column.indexOf("greatest(") != -1
                || column.indexOf("least(") != -1
                || column.indexOf("ln") != -1
                || column.indexOf("log(") != -1
                || column.indexOf("log10(") != -1
                || column.indexOf("log2(") != -1
                || column.indexOf("max(") != -1
                || column.indexOf("min(") != -1
                || column.indexOf("mod(") != -1
                || column.indexOf("pi(") != -1
                || column.indexOf("pow(") != -1
                || column.indexOf("power(") != -1
                || column.indexOf("radians(") != -1
                || column.indexOf("rand(") != -1
                || column.indexOf("round(") != -1
                || column.indexOf("sign(") != -1
                || column.indexOf("sin(") != -1
                || column.indexOf("sqrt(") != -1
                || column.indexOf("sum(") != -1
                || column.indexOf("tan(") != -1
                || column.indexOf("truncate(") != -1
        ){
            return "Integer";
        } else if (column.indexOf("adddate(") != -1
                || column.indexOf("addtime(") != -1
                || column.indexOf("curdate(") != -1
                || column.indexOf("current_date(") != -1
                || column.indexOf("current_time") != -1
                || column.indexOf("current_timestamp(") != -1
                || column.indexOf("curtime(") != -1
                || column.indexOf("date(") != -1
                || column.indexOf("datediff(") != -1
                || column.indexOf("date_add(") != -1
                || column.indexOf("date_format(") != -1
                || column.indexOf("date_sub(") != -1
                || column.indexOf("day(") != -1
                || column.indexOf("dayname(") != -1
                || column.indexOf("dayofmonth(") != -1
                || column.indexOf("dayofweek(") != -1
                || column.indexOf("DAYOFYEAR(") != -1
                || column.indexOf("extract(") != -1
                || column.indexOf("from_days(") != -1
                || column.indexOf("hour(") != -1
                || column.indexOf("last_day(") != -1
                || column.indexOf("localtime(") != -1
                || column.indexOf("localtimestamp(") != -1
                || column.indexOf("makedate(") != -1
                || column.indexOf("maketime(") != -1
                || column.indexOf("microsecond(") != -1
                || column.indexOf("minute(") != -1
                || column.indexOf("monthname(") != -1
                || column.indexOf("month(") != -1
                || column.indexOf("now(") != -1
                || column.indexOf("period_add(") != -1
                || column.indexOf("period_diff(") != -1
                || column.indexOf("quarter(") != -1
                || column.indexOf("second(") != -1
                || column.indexOf("sec_to_time(") != -1
                || column.indexOf("str_to_date(") != -1
                || column.indexOf("subdate(") != -1
                || column.indexOf("subtime(") != -1
                || column.indexOf("sysdate(") != -1
                || column.indexOf("time(") != -1
                || column.indexOf("time_format(") != -1
                || column.indexOf("time_to_sec(") != -1
                || column.indexOf("timediff(") != -1
                || column.indexOf("timestamp(") != -1
                || column.indexOf("timestampdiff(") != -1
                || column.indexOf("to_days(") != -1
                || column.indexOf("week(") != -1
                || column.indexOf("weekday(") != -1
                || column.indexOf("weekofyear(") != -1
                || column.indexOf("year(") != -1
                || column.indexOf("yearweek(") != -1
        ){
            return "Date";
        } else {
            return "String";
        }
    }


    public static String getSqlFrom(String sql) {
        StringBuffer out = new StringBuffer();
        try {
            //使用mysql解析
            MySqlStatementParser sqlStatementParser = new MySqlStatementParser(sql);
            //解析select查询
            SQLSelectStatement sqlStatement = (SQLSelectStatement) sqlStatementParser.parseSelect();
            SQLSelect sqlSelect = sqlStatement.getSelect();
            //获取sql查询块
            SQLSelectQueryBlock sqlSelectQuery = (SQLSelectQueryBlock) sqlSelect.getQuery();

            //创建sql解析的标准化输出
            SQLASTOutputVisitor sqlastOutputVisitor = SQLUtils.createFormatOutputVisitor(out, null, JdbcUtils.MYSQL);

            //解析select项
            out.delete(0, out.length());
            for (SQLSelectItem sqlSelectItem : sqlSelectQuery.getSelectList()) {
                if (out.length() > 1) {
                    out.append(",");
                }
                sqlSelectItem.accept(sqlastOutputVisitor);
            }
            //解析from
            out.delete(0, out.length());
            sqlSelectQuery.getFrom().accept(sqlastOutputVisitor);
//        System.out.println("FROM " + out);

            //解析where
//        out.delete(0, out.length());
//        sqlSelectQuery.getWhere().accept(sqlastOutputVisitor);
//        System.out.println("WHERE " + out);
        } catch (Exception e) {
            if (sql.indexOf("dual") != -1) {
                return "dual";
            }
        }
        return out.toString();
    }

    /**
     *  detect table names from given table
     *  ATTENTION : WE WILL SKIP SCALAR SUBQUERY IN PROJECTION CLAUSE
     * */
    public static List<String> getTableNames(String sql) throws Exception {
        List<String> tablenames = null;
        try {
            TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
            Statement statement = pm.parse(new StringReader(sql));
            if (statement instanceof Select) {
                tablenames = tablesNamesFinder.getTableList(statement);
            } else if (statement instanceof Update) {
                return null;
            } else if (statement instanceof Delete) {
                return null;
            } else if (statement instanceof Replace) {
                return null;
            } else if (statement instanceof Insert) {
                return null;
            }
        } catch (Exception e) {
            if (sql.indexOf("dual") != -1) {
                tablenames = new ArrayList<>();
                tablenames.add("dual");
            }
        }
        return tablenames;
    }
    /**
     * 获取sql语句中查询字段
     *
     * @param sql
     * @return
     */
    public static List<String> getSelectColumns(String sql) {
        MySqlStatementParser mySqlStatementParser = new MySqlStatementParser(sql);
        SQLSelectStatement sqlSelectStatement = (SQLSelectStatement) mySqlStatementParser.parseSelect();

        SQLSelect sqlSelect = sqlSelectStatement.getSelect();
        SQLSelectQuery sqlSelectQuery = sqlSelect.getQuery();
        if (sqlSelectQuery instanceof MySqlSelectQueryBlock) {
            MySqlSelectQueryBlock mySqlSelectQueryBlock = (MySqlSelectQueryBlock) sqlSelectQuery;
            //   获取查询字段
            List<SQLSelectItem> selectList = mySqlSelectQueryBlock.getSelectList();
            List<String> columns = new ArrayList<>();
            for (SQLSelectItem sqlSelectItem : selectList) {
                columns.add(sqlSelectItem.toString());
            }
            return columns;
        }
        return null;
    }



}
